******SET YOUR DIRECTORY to ...\replication



clear all
set more off


set matsize 2000

***From bridge obtain place-county-msa link
use "intermediatedata\bridge_final.dta", clear

drop if FMSA==9999

gen place_cc=FPLACE if city_center==1

*Generate FIPS county code
gen fipscounty="0"+string(FSTATE)+"00"+string(FCOUNTY) if FSTATE<10 & FCOUNTY<10
replace fipscounty=string(FSTATE)+"00"+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY<10
replace fipscounty="0"+string(FSTATE)+"0"+string(FCOUNTY) if FSTATE<10 & FCOUNTY>=10 & FCOUNTY<100
replace fipscounty=string(FSTATE)+"0"+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY>=10 & FCOUNTY<100
replace fipscounty="0"+string(FSTATE)+string(FCOUNTY) if FSTATE<10 & FCOUNTY>=10 & FCOUNTY>=100 & FCOUNTY!=.
replace fipscounty=string(FSTATE)+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY>=10 & FCOUNTY>=100 & FCOUNTY!=.
destring fipscounty, replace force
drop if fipscounty==.

*Generate FIPS place code
gen fipsplace_00="0"+string(FSTATE)+"000"+string(place_cc) if FSTATE<10 & place_cc<100
replace fipsplace_00=string(FSTATE)+"000"+string(place_cc) if FSTATE>=10 & FSTATE!=. & place_cc<100
replace fipsplace_00="0"+string(FSTATE)+"00"+string(place_cc) if FSTATE<10 & place_cc<1000 & place_cc>=100
replace fipsplace_00=string(FSTATE)+"00"+string(place_cc) if FSTATE>=10 & FSTATE!=. & place_cc<1000 & place_cc>=100
replace fipsplace_00="0"+string(FSTATE)+"0"+string(place_cc) if FSTATE<10 & place_cc<10000 & place_cc>=1000
replace fipsplace_00=string(FSTATE)+"0"+string(place_cc) if FSTATE>=10 & FSTATE!=. & place_cc<10000 & place_cc>=1000
replace fipsplace_00="0"+string(FSTATE)+string(place_cc) if FSTATE<10 & place_cc!=. & place_cc>=10000
replace fipsplace_00=string(FSTATE)+string(place_cc) if FSTATE>=10 & FSTATE!=. & place_cc!=. & place_cc>=10000
destring fipsplace_00, replace force

collapse (mean) fipsplace_00, by(fipscounty FMSA)

*Drop counties not in MSA
drop if FMSA==.

*Drop if MSA does not have city center
egen check=mean(fipsplace_00), by(FMSA)
drop if check==.
drop check

***Merge with  CBP county employment
joinby fipscounty using "intermediatedata\data_cbp_cny.dta"

rename fipscounty fipscounty_00

*Generate sector
gen superind=1 if sic==7
replace superind=2 if sic==10 | sic==15 | sic==20
replace superind=3 if (sic>40 & sic<99)
replace superind=4 if sic==99
label define superindl 1 "Agriculture"  2 "Goods producing industries" 3 "Service producing industries" 4 "Other industries"
label values superind superindl 

*Generate two digit
gen sic_digit_2=substr(string(sic), -2,.) if sic>=1000
gen sic_digit=2 if sic==0 | sic==7 | sic==10 | sic==15 | sic==20 | ///
        sic==40 | sic==50 | sic==52 | sic==60 | sic==70 | ///
		sic==99
replace sic_digit=3 if sic_digit_2=="00"
replace sic_digit=4 if sic_digit_2!="00" & sic_digit_2!=""
drop sic_digit_2
drop if sic_digit==4

*Compute proportion of employment in central county
levelsof sic if sic_digit==2, local(levels)
foreach l of local levels {
egen emp_msa_sic`l'=total(emp) if sic==`l', by(FMSA year)
g emp_ctycc_sic`l'= emp if sic==`l'
gen perc_empl_ctycc_sic`l'=emp/emp_msa_sic`l' if fipsplace_00!=.
replace perc_empl_ctycc_sic`l'=1 if emp==0 & emp_msa_sic`l'==0 & fipsplace_00!=.
}
levelsof sic if sic_digit==3, local(levels)
foreach l of local levels {
egen emp_msa_sic`l'=total(emp) if sic==`l', by(FMSA year)
g emp_ctycc_sic`l'= emp if sic==`l'
gen perc_empl_ctycc_sic`l'=emp/emp_msa_sic`l' if fipsplace_00!=.
replace perc_empl_ctycc_sic`l'=1 if emp==0 & emp_msa_sic`l'==0 & fipsplace_00!=.
}



collapse (mean) perc_* emp_msa_sic* emp_ctycc_sic*  ,by(fipsplace_00 year)

save rawdata\CBPCounty\data_cbp_msa.dta, replace
